import pymysql
from db_utils import con_my_sql
from config import config_instance


unit_permission_conn = pymysql.connect(**config_instance.unit_permission_conn_config)


# 根据用户ID获取所有角色
def get_user_roles(user_id):
    query = "SELECT r.role_name FROM Roles r JOIN User_Role ur ON r.role_id = ur.role_id WHERE ur.user_id = %s"
    try:
        cursor_result = con_my_sql(unit_permission_conn, query, (user_id,))
        roles = [row['role_name'] for row in cursor_result]
        # print("get_user_roles: ", roles)
        return roles
    except Exception as e:
        print(e)
        return []


# 根据用户ID获取所有权限
def get_user_permissions(user_id):
    query = "SELECT p.permission_name FROM Permissions p JOIN Role_Permission rp ON p.permission_id = rp.permission_id JOIN User_Role ur ON rp.role_id = ur.role_id WHERE ur.user_id = %s"
    try:
        cursor_result = con_my_sql(unit_permission_conn, query, (user_id,))
        permissions = [row['permission_name'] for row in cursor_result]
        # print("get_user_permissions: ", permissions)
        return permissions
    except Exception as e:
        print(e)
        return []

# 新增方法：获取所有用户的角色和权限字典
def get_all_users_roles_permissions():
    all_users_info = {}
    query_get_users = "SELECT UserID FROM users"
    try:
        user_ids = con_my_sql(unit_permission_conn, query_get_users, ())
        for row in user_ids:
            user_id = row['UserID']
            roles = get_user_roles(user_id)
            permissions = get_user_permissions(user_id)
            all_users_info[user_id] = {
                "roles": roles,
                "permissions": permissions
            }
    except Exception as e:
        print(e)
    return all_users_info

# 新增方法：给指定用户ID添加角色
def add_role_to_user(user_id, role_name):
    unique_roles = ['sysAdmin','secAdmin','secAuditor']
    existing_roles = get_user_roles(user_id)
    if role_name in unique_roles and role_name in existing_roles:
        print(f"用户 {user_id} 已拥有唯一角色 {role_name}，无法重复添加。")
        return False

    # 这里假设你的数据库中Roles表有role_name字段，User_Role表有user_id和role_id字段，先查询要添加角色的role_id
    query_get_role_id = "SELECT role_id FROM Roles WHERE role_name = %s"
    try:
        role_id_result = con_my_sql(unit_permission_conn, query_get_role_id, (role_name,))
        if role_id_result:
            role_id = role_id_result[0]['role_id']
            # 插入用户角色关联数据到User_Role表
            insert_query = "INSERT INTO User_Role (user_id, role_id) VALUES (%s, %s)"
            con_my_sql(unit_permission_conn, insert_query, (user_id, role_id))
            print(f"成功为用户 {user_id} 添加角色 {role_name}。")
            return True
        else:
            print(f"角色 {role_name} 在数据库中不存在，无法添加。")
            return False
    except Exception as e:
        print(e)
        return False

# 示例调用
if __name__ == "__main__":
    # user_id = 1
    # roles = get_user_roles(user_id)
    # permissions = get_user_permissions(user_id)
    #
    # print("Roles:", roles)
    # print("Permissions:", permissions)
    all_users_data = get_all_users_roles_permissions()
    print("All Users' Roles and Permissions:", all_users_data)
    # 示例：给用户ID为6的用户添加审核员角色
    add_role_to_user(1, 'auditor')

